﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DSPL.Contracts;
using DSPL.Contracts.Common;
using DSPL.Contracts.Interfaces;
using DSPL.DataAccess;

namespace DSPL.Services.Services
{
    public class WriteService : IWriteService
    {
        DatabaseAccess db = DatabaseAccess.Instance ;
        public int PrimaryKey { get; set; }

        #region IWriteService Members

        public bool InsertMasterData(string tablename, string primaryKedField,DataRow newDataRow, ref SqlTransaction sqlTran)
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            
            foreach (DataColumn item in newDataRow.Table.Columns)
            {
                if ((item.ColumnName!=primaryKedField) && !GlobalVariables.MasterInsertIgnoreList.Contains(item.ColumnName.ToUpper()))
                {
                    columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(newDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : newDataRow[item.ColumnName] });
                }
            }
            
            PrimaryKey = GetPrimaryKey(tablename, ref sqlTran);

            columnList.Add(new ColumnWithValue() { ColumnName = primaryKedField, ColumnValue = PrimaryKey });
            columnList.Add(new ColumnWithValue() { ColumnName = "AddUser", ColumnValue = GlobalVariables.LoggedInUser });
            columnList.Add(new ColumnWithValue() { ColumnName = "AddDate", ColumnValue = DateTime.Now });
            columnList.Add(new ColumnWithValue() { ColumnName = "AddMach", ColumnValue = GlobalVariables.MachineName });

            Query query = new Query() { TableName = tablename, ColValues = columnList.ToArray(), QType = QueryType.INSERT };

            object objResult = db.Excecute(query, ref  sqlTran,true);

            return objResult != null;
        }

        

        public bool UpdateMasterData(string tableName,string primaryKedField, string logTableName, DataRow newDataRow, DataRow oldDataRow, ref  SqlTransaction sqlTran)
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            foreach (DataColumn item in oldDataRow.Table.Columns)
            {
                if ((!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper())))
                {
                    columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(oldDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : oldDataRow[item.ColumnName] });
                }
            }
            columnList.Add(new ColumnWithValue() { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelDate", ColumnValue = DateTime.Now, ColumnDataType = QDataType.DATETIME });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelMach", ColumnValue = GlobalVariables.MachineName, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelType", ColumnValue = "E", ColumnDataType = QDataType.TEXT });

            Query query = new Query() { TableName = logTableName, ColValues = columnList.ToArray(), QType = QueryType.INSERT };
            object objResult = db.Excecute(query, ref  sqlTran, true);
            if (objResult != null)
            {
                columnList = new List<ColumnWithValue>();

                foreach (DataColumn item in newDataRow.Table.Columns)
                {
                    if ((item.ColumnName!=primaryKedField) && !GlobalVariables.MasterUpdateIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(newDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : newDataRow[item.ColumnName] });
                    }
                }

                List<ColumnWithValue> whereClause = new List<ColumnWithValue>();
                whereClause.Add(new ColumnWithValue() { ColumnName = primaryKedField, ColumnValue = newDataRow[primaryKedField], ColumnDataType = QDataType.NUMERIC });

                query = new Query() { TableName = tableName, ColValues = columnList.ToArray(), QType = QueryType.UPDATE, WhereClause = whereClause.ToArray() };

                objResult = db.Excecute(query, ref  sqlTran, true);
            }

            return objResult != null;
        }

        public bool DeleteMasterData(string tableName, string primaryKedField, string logTableName, DataRow oldDataRow, ref  SqlTransaction sqlTran)
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            foreach (DataColumn item in oldDataRow.Table.Columns)
            {
                if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                {
                    columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(oldDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : oldDataRow[item.ColumnName] });
                }
            }
            columnList.Add(new ColumnWithValue() { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelDate", ColumnValue = DateTime.Now, ColumnDataType = QDataType.DATETIME });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelMach", ColumnValue = GlobalVariables.MachineName, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelType", ColumnValue = "D", ColumnDataType = QDataType.TEXT });

            Query query = new Query() { TableName = logTableName, ColValues = columnList.ToArray(), QType = QueryType.INSERT };
            object objResult = db.Excecute(query, ref  sqlTran, true);
            if (objResult != null)
            {
                List<ColumnWithValue> whereClause = new List<ColumnWithValue>();
                whereClause.Add(new ColumnWithValue() { ColumnName = primaryKedField, ColumnValue = oldDataRow[primaryKedField], ColumnDataType = QDataType.NUMERIC });

                query = new Query() { TableName = tableName, QType = QueryType.DELETE, WhereClause = whereClause.ToArray() };

                objResult = db.Excecute(query, ref sqlTran, true);
            }

            return objResult != null;
        }

        public bool InsertTransactionData(string parentTableName, string childTableName, string primaryKedField, DataRow newDataRow, DataTable childDataTable, ref SqlTransaction sqlTran)
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            foreach (DataColumn item in newDataRow.Table.Columns)
            {
                if ((item.ColumnName != primaryKedField) && !GlobalVariables.MasterInsertIgnoreList.Contains(item.ColumnName.ToUpper()))
                {
                    columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(newDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : newDataRow[item.ColumnName] });
                }
            }

            PrimaryKey = GetPrimaryKey(parentTableName, ref sqlTran);

            columnList.Add(new ColumnWithValue() { ColumnName = primaryKedField, ColumnValue = PrimaryKey });
            columnList.Add(new ColumnWithValue() { ColumnName = "AddUser", ColumnValue = GlobalVariables.LoggedInUser });
            columnList.Add(new ColumnWithValue() { ColumnName = "AddDate", ColumnValue = DateTime.Now });
            columnList.Add(new ColumnWithValue() { ColumnName = "AddMach", ColumnValue = GlobalVariables.MachineName });

            Query query = new Query() { TableName = parentTableName, ColValues = columnList.ToArray(), QType = QueryType.INSERT };

            object objResult = db.Excecute(query, ref  sqlTran, true);

            return objResult != null;
        }



        public bool UpdateTransactionData(string tableName, string primaryKedField, string logTableName, DataRow newDataRow, DataRow oldDataRow, ref  SqlTransaction sqlTran)
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            foreach (DataColumn item in oldDataRow.Table.Columns)
            {
                if ((!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper())))
                {
                    columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(oldDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : oldDataRow[item.ColumnName] });
                }
            }
            columnList.Add(new ColumnWithValue() { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelDate", ColumnValue = DateTime.Now, ColumnDataType = QDataType.DATETIME });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelMach", ColumnValue = GlobalVariables.MachineName, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelType", ColumnValue = "E", ColumnDataType = QDataType.TEXT });

            Query query = new Query() { TableName = logTableName, ColValues = columnList.ToArray(), QType = QueryType.INSERT };
            object objResult = db.Excecute(query, ref  sqlTran, true);
            if (objResult != null)
            {
                columnList = new List<ColumnWithValue>();

                foreach (DataColumn item in newDataRow.Table.Columns)
                {
                    if ((item.ColumnName != primaryKedField) && !GlobalVariables.MasterUpdateIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(newDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : newDataRow[item.ColumnName] });
                    }
                }

                List<ColumnWithValue> whereClause = new List<ColumnWithValue>();
                whereClause.Add(new ColumnWithValue() { ColumnName = primaryKedField, ColumnValue = newDataRow[primaryKedField], ColumnDataType = QDataType.NUMERIC });

                query = new Query() { TableName = tableName, ColValues = columnList.ToArray(), QType = QueryType.UPDATE, WhereClause = whereClause.ToArray() };

                objResult = db.Excecute(query, ref  sqlTran, true);
            }

            return objResult != null;
        }

        public bool DeleteTransactionData(string tableName, string primaryKedField, string logTableName, DataRow oldDataRow, ref  SqlTransaction sqlTran)
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            foreach (DataColumn item in oldDataRow.Table.Columns)
            {
                if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                {
                    columnList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = string.IsNullOrEmpty(oldDataRow[item.ColumnName].ToString()) ? GetDefaultVlue(item.DataType.Name.ToUpper()) : oldDataRow[item.ColumnName] });
                }
            }
            columnList.Add(new ColumnWithValue() { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelDate", ColumnValue = DateTime.Now, ColumnDataType = QDataType.DATETIME });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelMach", ColumnValue = GlobalVariables.MachineName, ColumnDataType = QDataType.TEXT });
            columnList.Add(new ColumnWithValue() { ColumnName = "DelType", ColumnValue = "D", ColumnDataType = QDataType.TEXT });

            Query query = new Query() { TableName = logTableName, ColValues = columnList.ToArray(), QType = QueryType.INSERT };
            object objResult = db.Excecute(query, ref  sqlTran, true);
            if (objResult != null)
            {
                List<ColumnWithValue> whereClause = new List<ColumnWithValue>();
                whereClause.Add(new ColumnWithValue() { ColumnName = primaryKedField, ColumnValue = oldDataRow[primaryKedField], ColumnDataType = QDataType.NUMERIC });

                query = new Query() { TableName = tableName, QType = QueryType.DELETE, WhereClause = whereClause.ToArray() };

                objResult = db.Excecute(query, ref sqlTran, true);
            }

            return objResult != null;
        }

        #endregion

        #region private Methods

        private int GetPrimaryKey(string tableName, ref  SqlTransaction sqlTran)
        {
            List<ParameterWithValue> paramList = new List<ParameterWithValue>();
            paramList.Add(new ParameterWithValue("@tableName", tableName));
            paramList.Add(new ParameterWithValue("@nextPk", 0, SqlDbType.Int, ParameterDirection.InputOutput));

            SpCaller spCaller = new SpCaller() { SpName = "SP_GET_PRIMARYKEY", SpParamValues = paramList.ToArray() };
            return (int)db.Excecute(spCaller, "@nextPk", ref sqlTran);
        }

        private object GetDefaultVlue(string DataType)
        {
            object returnValue = null;

            switch (DataType)
            {
                case "STRING":
                    returnValue = string.Empty;
                    break;

                case "DATETIME":
                    returnValue = new DateTime(1900, 01, 01); ;
                    break;

                case "BOOLEAN":
                    returnValue = false;
                    break;

                case "INT16":
                    returnValue = 0;
                    break;

                case "UINT16":
                    returnValue = 0;
                    break;

                case "INT32":
                    returnValue = 0;
                    break;

                case "UINT32":
                    returnValue = 0;
                    break;

                case "INT64":
                    returnValue = 0L;
                    break;

                case "UINT64":
                    returnValue = 0;
                    break;

                case "LONG":
                    returnValue = 0L;
                    break;

                case "DECIMAL":
                    returnValue = 0.0M;
                    break;

                case "DOUBLE":
                    returnValue = 0.0D;
                    break;

                case "BYTE":
                    returnValue = 0;
                    break;

                case "SBYTE":
                    returnValue = 0;
                    break;

                 case "CHAR":
                    returnValue = "\0";
                    break;

                 case "SINGLE":
                    returnValue = 0.0F;
                    break;

                default:
                    break;
            }

            return returnValue;
        }

        #endregion


    }
}
